﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Xml;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
namespace SSISTransferLoginsTask
{
    /// <summary>
    /// Task to copy logins from source to destination
    /// </summary>
    [DtsTask(DisplayName="CopyLogins",
        IconResource = "SSISTransferLoginsTask.Task.ico",
        TaskType="External",
        UITypeName = "SSISTransferLoginsTask.CopyLoginsUI,SSISTransferLoginsTask,Version=1.0.0.0,Culture=Neutral,PublicKeyToken=3d5350ba49bf9908")]
    public class CopyLoginsTask : Task , IDTSComponentPersist
    {
        private Connections _Connections;
        private string[] _SelectedLoginSDatabases=new string[0];
        
        /// <summary>
        /// Required method for a task, called whenever SSIS wants to validate this task.
        /// Typically before run, save and load
        /// </summary>
        /// <param name="connections">List of connection managers of the package</param>
        /// <param name="variableDispenser">Variable dispenser to work with variables</param>
        /// <param name="componentEvents">Used to generate events</param>
        /// <param name="log">Used for logging</param>
        /// <returns>DTSExecResult.Success or Failure</returns>
        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {
            if (SourceConnection == null)
            {
                componentEvents.FireError(0, "CopyLoginsTask", "Source connection is not filled in", "", 0);
                return DTSExecResult.Failure;
            }
            else if (DestinationConnection == null)
            {
                componentEvents.FireError(0, "CopyLoginsTask", "Destination connection is not filled in", "", 0);
                return DTSExecResult.Failure;
            }
            else if (DestinationConnection.ConnectionString == SourceConnection.ConnectionString)
            {
                componentEvents.FireError(0, "CopyLoginsTask", "Destination and source connection is the same", "", 0);
                return DTSExecResult.Failure;
            };
            return DTSExecResult.Success;
        }
        /// <summary>
        /// Called by SSIS during load so that this task can populate its properties
        /// </summary>
        /// <param name="node">The XML node representing this task</param>
        /// <param name="infoEvents">Used for reporting events</param>
        public void LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)
        {
            SourceConnection = Connections.Contains(node.Attributes["SourceConnection"].Value) ? Connections[node.Attributes["SourceConnection"].Value] : null;
            DestinationConnection = Connections.Contains(node.Attributes["DestinationConnection"].Value) ? Connections[node.Attributes["DestinationConnection"].Value] : null;
            if (node.Attributes["LoginsTransferred"].Value == LoginsTransferred.AllLogins.ToString())
                LoginsTransferred = LoginsTransferred.AllLogins;
            else if (node.Attributes["LoginsTransferred"].Value == LoginsTransferred.SelectedLogins.ToString())
                LoginsTransferred = LoginsTransferred.SelectedLogins;
            else if (node.Attributes["LoginsTransferred"].Value == LoginsTransferred.SelectedDatabases.ToString())
                LoginsTransferred = LoginsTransferred.SelectedDatabases;
            if (node.Attributes["IfObjectExists"].Value == IfObjectExists.Skipp.ToString())
                IfObjectExists = IfObjectExists.Skipp;
            else if (node.Attributes["IfObjectExists"].Value == IfObjectExists.Overwrite.ToString())
                IfObjectExists = IfObjectExists.Overwrite;
            else if (node.Attributes["IfObjectExists"].Value == IfObjectExists.Fail.ToString())
                IfObjectExists = IfObjectExists.Fail;
            CopySID = node.Attributes["CopySID"].Value == true.ToString() ? true : false;
            CopyPassword = node.Attributes["CopyPassword"].Value == true.ToString() ? true : false;
            StopOnError = node.Attributes["StopOnError"].Value == true.ToString() ? true : false;
            XmlNode selectedLoginsDatabases = node.GetElementsByTagName("SelectedLoginsDatabases")[0];
            SelectedLoginsDatabases = new string[selectedLoginsDatabases.ChildNodes.Count];
            int i = 0;
            foreach (XmlNode loginDatabase in selectedLoginsDatabases.ChildNodes)
            {
                SelectedLoginsDatabases[i] = loginDatabase.InnerText;
                i++;
            }
        }
        /// <summary>
        /// Called by SSIS to save the properties of this task in XML form
        /// </summary>
        /// <param name="doc">The XML doc where we have to save the properties of this task in</param>
        /// <param name="infoEvents">Used to report events</param>
        public void SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)
        {
            //create node in the package xml document
            XmlElement taskElement = doc.CreateElement(string.Empty, "CopyLoginsTask", string.Empty);

            // create attributes in the node that represent the custom properties and add each to the element
            // SourceConnection
            XmlAttribute sourceConnectionAttribute = doc.CreateAttribute(string.Empty, "SourceConnection", string.Empty);
            sourceConnectionAttribute.Value = SourceConnection == null ? "" : SourceConnection.Name;
            taskElement.Attributes.Append(sourceConnectionAttribute);
            // DestinationConnection
            XmlAttribute destinationConnectionAttribute = doc.CreateAttribute(string.Empty, "DestinationConnection", string.Empty);
            destinationConnectionAttribute.Value = DestinationConnection == null ? "" : DestinationConnection.Name;
            taskElement.Attributes.Append(destinationConnectionAttribute);
            // LoginsTransferred
            XmlAttribute loginsTransferredAttribute = doc.CreateAttribute(string.Empty, "LoginsTransferred", string.Empty);
            loginsTransferredAttribute.Value = LoginsTransferred.ToString();
            taskElement.Attributes.Append(loginsTransferredAttribute);
            // IfObjectExists
            XmlAttribute ifObjectExistsAttribute = doc.CreateAttribute(string.Empty, "IfObjectExists", string.Empty);
            ifObjectExistsAttribute.Value = IfObjectExists.ToString();
            taskElement.Attributes.Append(ifObjectExistsAttribute);
            // CopySID
            XmlAttribute copySIDAttribute = doc.CreateAttribute(string.Empty, "CopySID", string.Empty);
            copySIDAttribute.Value = CopySID.ToString();
            taskElement.Attributes.Append(copySIDAttribute);
            // CopyPassword
            XmlAttribute copyPasswordAttribute = doc.CreateAttribute(string.Empty, "CopyPassword", string.Empty);
            copyPasswordAttribute.Value = CopyPassword.ToString();
            taskElement.Attributes.Append(copyPasswordAttribute);
            // StopOnError
            XmlAttribute stopOnErrorAttribute = doc.CreateAttribute(string.Empty, "StopOnError", string.Empty);
            stopOnErrorAttribute.Value = StopOnError.ToString();
            taskElement.Attributes.Append(stopOnErrorAttribute);
            // SelectedLoginsDatabases
            XmlElement selectedLoginsDatabasesElement = doc.CreateElement("SelectedLoginsDatabases");
            XmlElement loginDatabaseElement;
            foreach (string loginDatabase in SelectedLoginsDatabases)
            {
                loginDatabaseElement = doc.CreateElement("LoginDatabase");
                loginDatabaseElement.InnerText = loginDatabase;
                selectedLoginsDatabasesElement.AppendChild(loginDatabaseElement);
            }
            taskElement.AppendChild(selectedLoginsDatabasesElement);

            doc.AppendChild(taskElement);
        }
        /// <summary>
        /// Called during execution of this task
        /// </summary>
        /// <param name="connections">The connections of the package containing this task</param>
        /// <param name="variableDispenser">Used to access the variables of the package</param>
        /// <param name="componentEvents">used for event reporting</param>
        /// <param name="log">Used for logging</param>
        /// <param name="transaction">Distributed transaction that we can be part of, this will not be used</param>
        /// <returns></returns>
        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            Server destination = (Server)DestinationConnection.AcquireConnection(null);
            SqlConnection sourceConn=null;
            SqlConnection destinationConn = null;
            try
            {
                sourceConn = new SqlConnection(((Server) SourceConnection.AcquireConnection(null)).ConnectionContext.ConnectionString);
                sourceConn.Open();
                Server destinationServer=(Server)DestinationConnection.AcquireConnection(null);
                destinationConn = new SqlConnection(destinationServer.ConnectionContext.ConnectionString);
                destinationConn.Open();

                foreach (Login login in GetLogins())
                {
                    bool fireAgain = true;
                    Exception error = null;
                    // Skip system logins because they can not be deleted
                    if (login.IsSystemObject)
                        continue;
                    // First try to create login, will fail if login already exists
                    error = CreateLogin(false,sourceConn, destinationConn, login);
                    if (error == null)
                    {
                        // Success
                        if (fireAgain)
                            componentEvents.FireInformation(0,
                             "CopyLoginsTask",
                            String.Format("LOGIN {0} Transferred", login.Name),
                            "", 0, ref fireAgain);
                    }
                    else if (ErrorIsAlreadyExist(error))
                    {
                        if (IfObjectExists == IfObjectExists.Overwrite)
                        {
                            error = CreateLogin(true, sourceConn, destinationConn, login);
                            if (error == null)
                            {
                                // Success
                                if (fireAgain)
                                    componentEvents.FireInformation(0,
                                     "CopyLoginsTask",
                                    String.Format("LOGIN {0} Transferred", login.Name),
                                    "", 0, ref fireAgain);
                            }
                            else if (StopOnError)
                            {
                                componentEvents.FireError(0, "CopyLoginsTask", TranslateErrorMessage(error), "", 0);
                                return DTSExecResult.Failure;
                            }
                            else 
                                componentEvents.FireError(0, "CopyLoginsTask", TranslateErrorMessage(error), "", 0);
                        }
                        else if (IfObjectExists == IfObjectExists.Fail)
                        {
                            componentEvents.FireError(0, "CopyLoginsTask", TranslateErrorMessage(error), "", 0);
                            return DTSExecResult.Failure;
                        };
                    }
                    else if (StopOnError)
                    {
                        componentEvents.FireError(0, "CopyLoginsTask", TranslateErrorMessage(error), "", 0);
                        return DTSExecResult.Failure;
                    }
                    else 
                        componentEvents.FireError(0, "CopyLoginsTask", TranslateErrorMessage(error), "", 0);
                }
            }
            finally
            {
                if (sourceConn!=null)
                    sourceConn.Close();
                if (destinationConn != null)
                    destinationConn.Close();
            }
            return DTSExecResult.Success;
        }
        /// <summary>
        /// Initialize the task, allows you to save everything that is linked to SSIS
        /// </summary>
        /// <param name="connections">The list of connection managers of the package</param>
        /// <param name="variableDispenser">Used to interact with the variables </param>
        /// <param name="events"></param>
        /// <param name="log"></param>
        /// <param name="eventInfos"></param>
        /// <param name="logEntryInfos"></param>
        /// <param name="refTracker"></param>
        public override void InitializeTask(Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents events, IDTSLogging log, EventInfos eventInfos, LogEntryInfos logEntryInfos, ObjectReferenceTracker refTracker)
        {
            _Connections = connections;
            base.InitializeTask(connections, variableDispenser, events, log, eventInfos, logEntryInfos, refTracker);
        }
        /// <summary>
        /// Check if exception means object already exist
        /// </summary>
        /// <param name="error"></param>
        /// <returns></returns>
        private bool ErrorIsAlreadyExist(Exception error)
        {
            return (error is SqlException) &&
                (((SqlException)error).Number == 15433 || ((SqlException)error).Number == 15025);
        }
        /// <summary>
        /// Construct error message
        /// </summary>
        /// <param name="ex">Exception to construct the error message for</param>
        /// <returns></returns>
        private string TranslateErrorMessage(Exception ex)
        {
            if (ex is SqlException)
            {
                StringBuilder msg = new StringBuilder(ex.Message + "\n");
                foreach (SqlError error in ((SqlException)ex).Errors)
                {
                    msg.AppendFormat("MsgId={0}, Error={1}, Sevirity={2}\n", error.Number, error.Message, error.Class);
                };
                return msg.ToString();
            }
            else
            {
                return ex.Message;
            }
        }
        /// <summary>
        /// Creates a new Loginn)
        /// </summary>
        /// <param name="source">The connection to the source (needed to retrieve the password)"</param>
        /// <param name="destination">The connection to the destination where the logon must be created</param>
        /// <param name="login">The logon to create</param>
        /// <returns>null or exception</returns>
        private Exception CreateLogin(bool deleteFirst,SqlConnection source,SqlConnection destination, Login login)
        {
            string initCmd = deleteFirst ? GetDeleteLogin(destination,login) : "";
            switch (login.LoginType)
            {
                case LoginType.SqlLogin:
                    return CreateSQLLogin(initCmd, source, destination, login);
                case LoginType.WindowsUser:
                    return CreateWindowsLogin(initCmd, destination, login);
                case LoginType.WindowsGroup:
                    return CreateWindowsLogin(initCmd, destination, login);
                case LoginType.AsymmetricKey:
                    return CreateAssymetricKeyLogin(initCmd, destination, login);
                case LoginType.Certificate:
                    return CreateCertificateLogin(initCmd, destination, login);
                default:
                    return null;
            }
        }
        /// <summary>
        /// Calculates the drop login statment, taking care also of case sensitive servers
        /// </summary>
        /// <param name="destination">The connection for the destination server</param>
        /// <param name="login">the login to drop</param>
        /// <returns>The string representing the delete statment</returns>
        private string GetDeleteLogin(SqlConnection destination,Login login)
        {
            if (login.LoginType == LoginType.WindowsGroup || login.LoginType == LoginType.WindowsUser)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("SELECT name FROM sys.server_principals WHERE sid=@sid", destination);
                    SqlParameter paramter = new SqlParameter("@sid", login.Sid);
                    cmd.Parameters.Add(paramter);
                    string name = (string)cmd.ExecuteScalar();
                    return String.Format("DROP LOGIN {0} ;", GetQuoted(name));
                }
                catch
                {
                    return String.Format("DROP LOGIN {0} ;", GetQuoted(login.Name));
                }

            }
            else
                return String.Format("DROP LOGIN {0} ;", GetQuoted(login.Name));
        }
        /// <summary>
        /// Creates a new Certificate Login)
        /// </summary>
        /// <param name="initialCmd">string containing stmts that must be exeucted before the create login</param>
        /// <param name="destination">The connection to the destination where the logon must be created</param>
        /// <param name="login">The logon to create</param>
        /// <returns>null or exception</returns>
        private Exception CreateAssymetricKeyLogin(string initCmd,SqlConnection destination, Login login)
        {
            StringBuilder cmd = new StringBuilder(initCmd);
            cmd.AppendFormat("CREATE LOGIN {0} FROM ASYMMETRIC KEY {1}",
                    GetQuoted(login.Name),
                    GetQuoted(login.AsymmetricKey));
            return CreateLoginExecute(cmd.ToString(), destination);
        }
        /// <summary>
        /// Creates a new Certificate Login
        /// </summary>
        /// <param name="initialCmd">string containing stmts that must be exeucted before the create login</param>
        /// <param name="destination">The connection to the destination where the logon must be created</param>
        /// <param name="login">The logon to create</param>
        /// <returns>null or exception</returns>
        private Exception CreateCertificateLogin(string initCmd,SqlConnection destination, Login login)
        {
            StringBuilder cmd = new StringBuilder(initCmd);
            cmd.AppendFormat("CREATE LOGIN {0} FROM CERTIFICATE {1}",
                    GetQuoted(login.Name),
                    GetQuoted(login.Certificate));
            return CreateLoginExecute(cmd.ToString(), destination);
        }
        /// <summary>
        /// Creates a new Windows Login
        /// </summary>
        /// <param name="initialCmd">string containing stmts that must be exeucted before the create login</param>
        /// <param name="destination">The connection to the destination where the logon must be created</param>
        /// <param name="login">The logon to create</param>
        /// <returns>null or exception</returns>
        private Exception CreateWindowsLogin(string initCmd,SqlConnection destination, Login login)
        {
            StringBuilder cmd = new StringBuilder(initCmd);
            bool withAdded=false;

            cmd.AppendFormat("CREATE LOGIN {0} FROM WINDOWS",
                    GetQuoted(login.Name));
            if (login.DefaultDatabase != null && login.DefaultDatabase != "")
            {
                cmd.AppendFormat(" WITH DEFAULT_DATABASE={0}",
                    GetQuoted(login.DefaultDatabase));
                withAdded=true;
            };
            if (login.Language != null && login.Language != "")
                if (withAdded)
                    cmd.AppendFormat(" ,DEFAULT_LANGUAGE={0}",
                        GetQuoted(login.Language));
                else
                    cmd.AppendFormat("WITH DEFAULT_LANGUAGE={0}",
                        GetQuoted(login.Language));

            return CreateLoginExecute(cmd.ToString(), destination);
        }

        /// <summary>
        /// Create a new SQL Login
        /// Moments this can fail are:
        ///     - Login is a sql login and has a credential associated that does not exists 
        ///       in the destination server
        ///     - Login is owner of a securable and we have to delete the login because overwrite
        ///       is specified
        ///     - Login is associated with a certificate/assymetric key and the certificate 
        ///       or assymetric key does not exist
        /// </summary>
        /// <param name="initialCmd">string containing stmts that must be exeucted before the create login</param>
        /// <param name="source">The connection to the source (needed to retrieve the password)"</param>
        /// <param name="destination">The connection to the destination where the logon must be created</param>
        /// <param name="login">The logon to create</param>
        /// <returns>null or exception</returns>
        private Exception CreateSQLLogin(string initCmd,SqlConnection source,SqlConnection destination, Login login)
        {
            StringBuilder cmd = new StringBuilder(initCmd);
            cmd.AppendFormat("CREATE LOGIN {0} WITH PASSWORD={1} ",
                GetQuoted(login.Name),
                CopyPassword ? GetPasswordHash(source,login)+" HASHED" : "''");
            if (CopySID)
                cmd.AppendFormat(" ,SID={0}", GetHexString(login.Sid));
            if (login.DefaultDatabase != null && login.DefaultDatabase!="")
                cmd.AppendFormat(" ,DEFAULT_DATABASE={0}",
                    GetQuoted(login.DefaultDatabase));
            if (login.Language != null && login.Language!="")
                cmd.AppendFormat(" ,DEFAULT_LANGUAGE={0}",
                    GetQuoted(login.Language));
            if (login.IsPasswordExpired)
                cmd.Append(" ,CHECK_EXPIRATION=ON");
            else
                cmd.Append(" ,CHECK_EXPIRATION=OFF");
            if (login.PasswordPolicyEnforced)
                cmd.Append(" ,CHECK_POLICY=ON");
            else
                cmd.Append(" ,CHECK_POLICY=OFF");
            if (login.Credential != null && login.Credential != "")
                cmd.AppendFormat(" ,CREDENTIAL={0}",
                    GetQuoted(login.Credential));
            cmd.Append(";");
            if (!CopyPassword)
                cmd.AppendFormat("ALTER LOGIN {0} DISABLE",
                    GetQuoted(login.Name));
            return CreateLoginExecute(cmd.ToString(), destination);
        }
        /// <summary>
        /// Execute the statements in cmd
        /// </summary>
        /// <param name="cmd">The batch of statements to execute</param>
        /// <param name="destination">The connection to execute the statements on</param>
        /// <returns>null or exception</returns>
        private static Exception CreateLoginExecute(string cmd, SqlConnection destination)
        {
            // Create the login in one single transaction
            SqlTransaction transaction = null;
            try
            {
                transaction = destination.BeginTransaction();
                SqlCommand sqlCmd = new SqlCommand(cmd, destination);
                sqlCmd.Transaction = transaction;
                sqlCmd.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (Exception ex)
            {
                if (transaction != null)
                    transaction.Rollback();
                return ex;
            };
            return null;
        }
        /// <summary>
        /// Returns the password hash for a given login
        /// </summary>
        /// <param name="connection">Connection used to retrieve the password hash</param>
        /// <param name="login">The login to get the password hash from</param>
        /// <returns>The password hash</returns>
        private static  string GetPasswordHash(SqlConnection connection,Login login)
        {
            SqlCommand cmd = new SqlCommand("SELECT LOGINPROPERTY(@login,'PasswordHash')", connection);
            SqlParameter param = new SqlParameter("@login", login.Name);
            cmd.Parameters.Add(param);
            byte[] hash = (byte[])cmd.ExecuteScalar();
            StringBuilder strb = new StringBuilder();
            strb.Append(GetHexString(hash));
            return strb.ToString();
        }
        private static string GetHexString(byte[] bytes)
        {
            StringBuilder strb = new StringBuilder();
            strb.Append("0x");
            foreach (byte b in bytes)
                strb.Append(String.Format("{0,2:X}",b).Replace(' ','0'));
            return strb.ToString();
        }
        /// <summary>
        /// Returns the quoted identifier
        /// </summary>
        /// <param name="identifier">The identifier that must be quoted</param>
        /// <returns>The quoted identifier</returns>
        private static string GetQuoted(string identifier)
        {
            if (identifier.Contains(']'))
                return '"' + identifier + '"';
            else
                return '[' + identifier + ']';
        }


        private IEnumerable<Login> GetLogins()
        {
            Server server = (Server)SourceConnection.AcquireConnection(null);
            if (LoginsTransferred == LoginsTransferred.AllLogins)
            {
                foreach (Login login in server.Logins)
                {
                    yield return login;
                };
            }
            else if (LoginsTransferred == LoginsTransferred.SelectedLogins)
            {
                HashSet<string> selectedLogins = new HashSet<string>(SelectedLoginsDatabases);
                foreach (Login login in server.Logins)
                {
                    if (selectedLogins.Contains(login.Name))
                        yield return login;
                };
            }
            else if (LoginsTransferred == LoginsTransferred.SelectedDatabases)
            {
                HashSet<string> logins = new HashSet<string>();
                foreach (Database database in server.Databases)
                {
                    foreach (User user in database.Users)
                    {
                        if (user.Login != null)
                            logins.Add(user.Login);
                    }
                };
                foreach (Login login in server.Logins)
                {
                    if (logins.Contains(login.Name))
                        yield return login;
                }
            }
            yield break;
        }
        public ConnectionManager SourceConnection { get; set; }

        public ConnectionManager DestinationConnection { get; set; }

        public LoginsTransferred LoginsTransferred { get; set; }

        public IfObjectExists IfObjectExists { get; set; }

        public string[] SelectedLoginsDatabases
        {
            get
            {
                return _SelectedLoginSDatabases;
            }
            set
            {
                _SelectedLoginSDatabases = value;
            }
        }

        public bool CopySID { get; set; }

        public bool CopyPassword { get; set; }

        public bool StopOnError { get; set; }
        

        internal Connections Connections
        {
            get
            {
                return _Connections;
            }
        }

        
    }
    public enum LoginsTransferred
    {
        AllLogins,
        SelectedLogins,
        SelectedDatabases
    }
    public enum IfObjectExists
    {
        Skipp,
        Overwrite,
        Fail
    }
}
